package com.epoint.BookManager.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.alibaba.druid.util.StringUtils;
import com.epoint.BookManager.domain.BorrowInfo;
import com.epoint.BookManager.utils.JDBCUtil;

public class borrowInfoDao {

	/**
	 * 获取借阅列表
	 * 
	 * @param pageIndex
	 * @param pageSize
	 * @param sortField
	 * @param sortOrder
	 * @return
	 */
	public List<BorrowInfo> getBookList(int pageIndex, int pageSize, String sortField, String sortOrder) {

		Connection conn = JDBCUtil.getConnection();
		PreparedStatement pst = null;
		ResultSet rs = null;
		// 图书列表
		ArrayList<BorrowInfo> bookInfoList = new ArrayList<BorrowInfo>();
		ArrayList<Object> params = new ArrayList<>();
		StringBuffer buffer = new StringBuffer();

		buffer.append("select a.*,b.bookname from borrowinfo a left join bookinfo b on a.bookid=b.bookid where 1=1");
		if (!StringUtils.isEmpty(sortOrder) && !StringUtils.isEmpty(sortField)) {
			buffer.append(" order by " + sortField + " " + sortOrder);
		}
		if (-1 != pageIndex && -1 != pageSize) {
			buffer.append(" limit ? ,?");
			params.add(pageIndex * pageSize);
			params.add(pageSize);
		}
		try {
			pst = conn.prepareStatement(buffer.toString());
			int sizeCount = params.size();
			for (int i = 0; i < sizeCount; i++) {
				if ((sizeCount - i) <= 2) {
					pst.setInt(i + 1, Integer.parseInt(params.get(i).toString()));
				} else {
					pst.setString(i + 1, params.get(i).toString());
				}
			}
			rs = pst.executeQuery();
			while (rs.next()) {
				BorrowInfo bookInfo = new BorrowInfo();
				bookInfo.setBookid(rs.getString("bookid"));
				bookInfo.setBookname(rs.getString("bookname"));
				bookInfo.setBorrower(rs.getString("borrower"));
				bookInfo.setBorrowid(rs.getString("borrowid"));
				bookInfo.setReturntime(rs.getTimestamp("returntime"));
				bookInfo.setPhone(rs.getString("phone"));
				bookInfo.setBorrowtime(rs.getTimestamp("borrowtime"));
				bookInfoList.add(bookInfo);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.closeConnection(conn, pst, rs);
		}
		return bookInfoList;
	}

	/**
	 * 插入借阅信息
	 * 
	 * @param borrowInfo
	 * @return
	 */
	public int borrowBook(BorrowInfo borrowInfo) {
		String sql = "insert into borrowinfo (bookid,borrowid,borrower,phone,borrowtime,returntime) values(?,?,?,?,?,?)";
		String sql2 = "update bookinfo set remain = remain-1 where bookid = ?";
		int executeUpdate = 0;// sql插入借阅信息的返回影响行数
		int executeUpdate2 = 0;// sql2更新剩余数量影响的行数
		int result = 0;// 方法是否执行成功的返回值

		Connection conn = JDBCUtil.getConnection();
		PreparedStatement pst = null;
		String bookid = borrowInfo.getBookid();
		String borrower = borrowInfo.getBorrower();
		String borrowid = borrowInfo.getBorrowid();
		String phone = borrowInfo.getPhone();
		Date borrowtime = borrowInfo.getBorrowtime();
		Date returntime = borrowInfo.getReturntime();
		try {
			conn.setAutoCommit(false);
			pst = conn.prepareStatement(sql);
			pst.setString(1, bookid);
			pst.setString(2, borrowid);
			pst.setString(3, borrower);
			pst.setString(4, phone);
			pst.setObject(5, borrowtime);
			pst.setObject(6, returntime);
			executeUpdate = pst.executeUpdate();
			if (executeUpdate > 0) {
				pst.close();
				pst = conn.prepareStatement(sql2);
				pst.setString(1, bookid);
				executeUpdate2 = pst.executeUpdate();
			}
			if (executeUpdate > 0 && executeUpdate2 > 0) {
				conn.commit();
			} else {
				conn.rollback();
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.closeConnection(conn, pst, null);
		}
		return executeUpdate + executeUpdate2;
	}

	/**
	 * 还书
	 * 
	 * @param borrowid 借阅id
	 * @param bookid   图书id
	 * @return
	 */
	public int returnBook(String borrowid, String bookid) {
		String sql = "update borrowinfo set returntime = NOW() where borrowid = ?";
		String sql2 = "update bookinfo set remain = remain+1 where bookid = ?";
		int executeUpdate = 0; // sql更新借阅信息的返回影响行数
		int executeUpdate2 = 0;// sql2更新剩余数量影响的行数

		Connection conn = JDBCUtil.getConnection();
		PreparedStatement pst = null;
		try {
			conn.setAutoCommit(false);
			pst = conn.prepareStatement(sql);
			pst.setString(1, borrowid);
			executeUpdate = pst.executeUpdate();
			if (executeUpdate > 0) {
				pst.close();
				pst = conn.prepareStatement(sql2);
				pst.setString(1, bookid);
				executeUpdate2 = pst.executeUpdate();
			}
			if (executeUpdate > 0 && executeUpdate2 > 0) {
				conn.commit();
			} else {
				conn.rollback();
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.closeConnection(conn, pst, null);
		}
		return executeUpdate + executeUpdate2;
	}

	/**
	 * 
	 * 获取当前数据库中的最大借阅号
	 * 
	 * @return
	 */

	public String getBorrorID() {

		Connection conn = JDBCUtil.getConnection();
		PreparedStatement pst = null;
		ResultSet rs = null;
		String maxNo = "";
		String sql = "select ifnull(max(substr(borrowid,11,4)),'0000') from borrowinfo where borrowid like ?";

		try {
			pst = conn.prepareStatement(sql);

			pst.setString(1, "Borrow%");

			rs = pst.executeQuery();
			while (rs.next()) {
				maxNo = rs.getString(1);
			}

		} catch (SQLException e) {

			e.printStackTrace();
		}finally {
			JDBCUtil.closeConnection(conn, pst, rs);
		}
		return maxNo;
	}

	/**
	 * 删除借阅记录
	 * 
	 * @param borrwid
	 * @return
	 */
	public int deleteBorrowinfo(String borrwid) {

		Connection conn = JDBCUtil.getConnection();
		PreparedStatement pst = null;
		String sql = "delete from borrowinfo where borrowid = ?";
		int executeUpdate = 0;
		try {
			pst = conn.prepareStatement(sql);
			pst.setString(1, borrwid);
			executeUpdate = pst.executeUpdate();

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return executeUpdate;
	}

}
